﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace InHD.utilities
{
    class DBUltils
    {
        private static SqlConnection conn;
        public DBUltils()
        {
        }

        public bool connectDB(string userName, string passWord)
        {
            String strConnect = "Data Source=" + Constants.Security.DefaultServer + "; Network Library=DBMSSOCN; Initial Catalog=inhd;User ID=" + userName + ";Password=" + passWord + ";";
            conn = new SqlConnection(strConnect);
            try
            {
                conn.Open();
            }
            catch (Exception exp)
            {
                return false;
            }
            return true;
        }

        public DataSet queryDB(string storeProc, string[] paramName, ParamType[] type, object[] value, QueryType queryType)
        {
            DataSet ds = new DataSet();
            if (conn == null)
                return ds;
            SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(" + storeProc + ")" +"  and OBJECTPROPERTY(id, N'IsProcedure') = 1", conn);
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter(storeProc, conn);
            MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (paramName != null)
            {
                for (int i = 0; i < paramName.Length; i++)
                {
                    switch (type[i])
                    {
                        case ParamType.Str:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.VarChar, 255));
                            break;
                        case ParamType.NStr:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.NVarChar, 255));
                            break;
                        case ParamType.LNStr:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.NVarChar, 1000));
                            break;
                        case ParamType.Int:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.Int, 4));
                            break;
                        case ParamType.Real:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.Real, 8));
                            break;
                        case ParamType.DateTime:
                            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName[i], SqlDbType.DateTime, 100));
                            break;
                        default:
                            break;
                    }
                    MyDataAdapter.SelectCommand.Parameters[paramName[i]].Value = value[i];
                }
            }
            //Create and add an output parameter to the Parameters collection. 
            if (queryType == QueryType.Select)
            {
                MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
                MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;
            }
            //Set the direction for the parameter. This parameter returns the Rows that are returned.
            MyDataAdapter.Fill(ds, storeProc);

            return ds;
        }

        public static int CRUDQuery(string storeProcedureName, List<string> paramName, List<object> paramValue)
        {
            int newCarID = 0;
            String strConnect = "Data Source=" + Constants.Security.DefaultServer + "; Network Library=DBMSSOCN; Initial Catalog=inhd;User ID=" + Constants.Security.DefaultDBUser + ";Password=" + Constants.Security.DefaultDBPassword + ";";
            SqlConnection connection = new SqlConnection(strConnect);
            try
            {
                SqlCommand cmd = new SqlCommand(storeProcedureName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                for (int i = 0; i < paramName.Count; i++)
                {
                    cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
                }
                connection.Open();
                newCarID = Convert.ToInt32(cmd.ExecuteScalar());
                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
            return newCarID;
        }

        public static void RunStoreProcedure(string storeProcedureName, string[] paramName, object[] paramValue, SqlConnection connection, SqlTransaction myTransaction)
        {
            int rowEffected;

            SqlCommand cmd = new SqlCommand(storeProcedureName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Transaction = myTransaction;

            for (int i = 0; i < paramName.Length; i++)
            {
                cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
            }
            rowEffected = Convert.ToInt32(cmd.ExecuteNonQuery());
        }

        public enum ParamType{
            Int,
            Str,
            NStr,
            LNStr,
            Real,
            DateTime
        }

        public enum QueryType
        {
            Insert,
            Update,
            Delete,
            Select
        }

        public bool closeDB()
        {
            if (conn == null)
            {
                return false;
            }
            else
            {
                conn.Close();
            }
                
            return true;
        }

    }
}
